package com.qianfeng.commons;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 公共的CRUD
 * 
 * @author ChenZeTao 2018年9月14日 上午9:25:28
 */
public class DBUtils<T> {

	private static Connection connection;
	private static PreparedStatement pst;
	private static ResultSet rs;
	private static ResultSetMetaData metaData;

	/**
	 * 公共的增删改方法
	 */
	public static int commonOper(String sql, Object... params) {

		try {
			// update xx set name= ?,age = ? where id = ?
			// delete from xx where id = ?
			connection = DBManager.getConnection();
			// 开启事务
			connection.setAutoCommit(false);
			pst = connection.prepareStatement(sql);
			for (int i = 0; i < params.length; i++) {
				pst.setObject(i + 1, params[i]);
			}
			int result = pst.executeUpdate();
			System.out.println("受影响的行数:" + result);
			// 提交事务
			connection.commit();
			return result;
		} catch (SQLException e) {
			e.printStackTrace();
			// 回滚事务
			try {
				connection.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			// 关闭资源
			DBManager.closeAll(pst, connection);
		}

		return -1;
	}

	// 查询集合数据
	public List<T> getList(String sql, Class<T> clazz, Object... params) {
		List<T> list = new ArrayList<>();
		// select * from teacher
		try {
			connection = DBManager.getConnection();
			pst = connection.prepareStatement(sql);
			for (int i = 0; i < params.length; i++) {
				pst.setObject(i + 1, params[i]);
			}
			rs = pst.executeQuery();
			// 这个对象得到结果集中字段的信息(字段的个数，字段的名称，类型。。)
			metaData = rs.getMetaData();
			// 得到结果集中字段的个数
			int columnCount = metaData.getColumnCount();
			while (rs.next()) {
				T ins = clazz.newInstance();
				for (int i = 1; i <= columnCount; i++) {
					// 得到字段的名称
					String columnName = metaData.getColumnName(i);
					// 得到字段的值
					Object value = rs.getObject(i);

					// Field field = clazz.getDeclaredField("name");
					// 得到属性对应的field对象
					Field field = clazz.getDeclaredField(columnName);
					// 设置访问权限
					field.setAccessible(true);
					// 给属性赋值
					field.set(ins, value);
				}
				list.add(ins);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (NoSuchFieldException e) {
			e.printStackTrace();
		} catch (SecurityException e) {
			e.printStackTrace();
		}
		return list;
	}

	// 查询单个对象数据
	public T getSingleInstance(String sql, Class<T> clazz, Object... params) {
		// select * from teacher
		try {
			connection = DBManager.getConnection();
			pst = connection.prepareStatement(sql);
			for (int i = 0; i < params.length; i++) {
				pst.setObject(i + 1, params[i]);
			}
			rs = pst.executeQuery();
			// 这个对象得到结果集中字段的信息(字段的个数，字段的名称，类型。。)
			metaData = rs.getMetaData();
			// 得到结果集中字段的个数
			int columnCount = metaData.getColumnCount();
			if (rs.next()) {
				T ins = clazz.newInstance();
				for (int i = 1; i <= columnCount; i++) {
					// 得到字段的名称
					String columnName = metaData.getColumnName(i);
					// 得到字段的值
					Object value = rs.getObject(i);

					// Field field = clazz.getDeclaredField("name");
					// 得到属性对应的field对象
					Field field = clazz.getDeclaredField(columnName);
					// 设置访问权限
					field.setAccessible(true);
					// 给属性赋值
					field.set(ins, value);
				}
				return ins;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (NoSuchFieldException e) {
			e.printStackTrace();
		} catch (SecurityException e) {
			e.printStackTrace();
		}
		return null;
	}
	// 查询单个对象数据
		public int getCount(String sql, Object... params) {
			int count = 0;
			try {
				connection = DBManager.getConnection();
				pst = connection.prepareStatement(sql);
				for (int i = 0; i < params.length; i++) {
					pst.setObject(i + 1, params[i]);
				}
				rs = pst.executeQuery();
				if (rs.next()) {
					count = rs.getInt(1);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (SecurityException e) {
				e.printStackTrace();
			}
			return count;
		}
}
